package br.com.northwind.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Date;
import java.util.ArrayList;
import java.util.List;

import br.com.northwind.database.Conexao;
import br.com.northwind.exceptions.ConexaoException;
import br.com.northwind.exceptions.DriverException;
import br.com.northwind.fabricas.FabricaConexao;
import br.com.northwind.interfaces.IEmployeesDao;
import br.com.northwind.tabelas.Employees;

public class EmployeesDao implements IEmployeesDao {

	private final String SQL_INSERT = "INSERT INTO Employees ( LastName, FirstName, Title, TitleOfCourtesy, BirthDate, HireDate, Address, City, Region, PostalCode, Country, HomePhone, Extension, Photo, Notes, ReportsTo ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
	private final String SQL_UPDATE = "UPDATE Employees SET LastName = ?, FirstName = ?, Title = ?, TitleOfCourtesy = ?, BirthDate = ?, HireDate = ?, Address = ?, City = ?, Region = ?, PostalCode = ?, Country = ?, HomePhone = ?, Extension = ?, Photo = ?, Notes = ?, ReportsTo = ? WHERE CustomerId = ?";
	private final String SQL_DELETE = "DELETE FROM Employees WHERE EmployeeId = ?";
	private final String SQL_SELECT_TODOS = "SELECT * FROM Employees";
	private final String SQL_SELECT_BY_CODIGO = "SELECT * FROM Employees WHERE EmployeeId = ?";
	
	private Conexao conexao;
	private PreparedStatement statement = null;
	private ResultSet rs = null;

	public EmployeesDao() {
		this.conexao = FabricaConexao.criarConexao();
	}

	public EmployeesDao(Conexao conexao) {
		this.conexao = conexao;
	}

	public void inserir(Employees _employeeid) throws ConexaoException,DriverException {
		try {
			this.conexao.abrirConexao();
			this.statement = this.conexao.getConexao().prepareStatement(this.SQL_INSERT);
			
			this.statement.setString(1, _employeeid.getLastName());
			this.statement.setString(2, _employeeid.getFirstName());
			this.statement.setString(3, _employeeid.getTitle());
			this.statement.setString(4, _employeeid.getTitleOfCourtesy());
			this.statement.setDate(5, _employeeid.getBirthDate());
			this.statement.setDate(6, _employeeid.getHireDate());
			this.statement.setString(7, _employeeid.getAddress());
			this.statement.setString(8, _employeeid.getCity());
			this.statement.setString(9, _employeeid.getRegion());
			this.statement.setString(10, _employeeid.getPostalCode());
			this.statement.setString(11, _employeeid.getCountry());
			this.statement.setString(12, _employeeid.getHomePhone());
			this.statement.setString(13, _employeeid.getExtension());
			this.statement.setString(14, _employeeid.getPhoto());
			this.statement.setString(15, _employeeid.getNotes());
			this.statement.setInt(16, _employeeid.getReportsTo());
			
			this.statement.execute();
		} catch (SQLException e) {
			throw new ConexaoException();
		} catch (ClassNotFoundException e) {
			throw new DriverException();
		} finally {
			try {
				
				if (this.conexao != null) this.conexao.fecharConexao();
				if (this.statement != null) this.statement.close();
				
			} catch (SQLException e) {
				throw new ConexaoException();
			}
		}
	}

	public void editar(Employees _employeeid) throws ConexaoException,DriverException {
		PreparedStatement update = null;
		try {
			this.conexao.abrirConexao();
			this.statement = this.conexao.getConexao().prepareStatement(this.SQL_SELECT_BY_CODIGO);
			
			this.statement.setInt(1, _employeeid.getEmployeeId());
			
			this.rs = statement.executeQuery();

			if (rs.next()) {
				update = this.conexao.getConexao().prepareStatement(this.SQL_UPDATE);

				update.setString(1, _employeeid.getFirstName());
				update.setString(2, _employeeid.getTitle());
				update.setString(3, _employeeid.getTitleOfCourtesy());
				update.setDate(4, _employeeid.getBirthDate());
				update.setDate(5, _employeeid.getHireDate());
				update.setString(6, _employeeid.getAddress());
				update.setString(7, _employeeid.getCity());
				update.setString(8, _employeeid.getRegion());
				update.setString(9, _employeeid.getPostalCode());
				update.setString(10, _employeeid.getCountry());
				update.setString(11, _employeeid.getHomePhone());
				update.setString(12, _employeeid.getExtension());
				update.setString(13, _employeeid.getPhoto());
				update.setString(14, _employeeid.getNotes());
				update.setInt(15, _employeeid.getReportsTo());

				update.execute();
				update.close();
			}
		} catch (SQLException e) {
			throw new ConexaoException();
		} catch (ClassNotFoundException e) {
			throw new DriverException();
		} finally {
			try {
				if (this.conexao != null) this.conexao.fecharConexao();
				if (this.statement != null) this.statement.close();
				if (this.rs != null) this.rs.close();
				if (update != null) update.close();
			} catch (SQLException e) {
				throw new ConexaoException();
			}
		}

	}

	public void excluir(Integer _employeeid) throws ConexaoException,DriverException {
		try {
			this.conexao.abrirConexao();
			this.statement = this.conexao.getConexao().prepareStatement(this.SQL_DELETE);
			
			this.statement.setInt(1, _employeeid);
			
			this.statement.execute();

		} catch (SQLException e) {
			throw new ConexaoException();
		} catch (ClassNotFoundException e) {
			throw new DriverException();
		} finally {
			try {
				
				if (this.conexao != null) this.conexao.fecharConexao();
				if (this.statement != null) this.statement.close();
				if (this.rs != null) this.rs.close();
				
			} catch (SQLException e) {
				throw new ConexaoException();
			}
		}
	}

	public List<Employees> selecionarAll() throws ConexaoException,DriverException {
		List<Employees> listaDeEmployees = new ArrayList<Employees>();
		try {
			this.conexao.abrirConexao();
			this.statement = this.conexao.getConexao().prepareStatement(this.SQL_SELECT_TODOS);
			this.rs = this.statement.executeQuery();

			while (rs.next()) {
				
				int EmployeeId = this.rs.getInt(1);
				String LastName = this.rs.getString(2);
				String FirstName = this.rs.getString(3);
				String Title = this.rs.getString(4);
				String TitleOfCourtesy = this.rs.getString(5);
				Date BirthDate = this.rs.getDate(6);
				Date HireDate = this.rs.getDate(7);
				String Address = this.rs.getString(8);
				String City = this.rs.getString(9);
				String Region = this.rs.getString(10);
				String PostalCode = this.rs.getString(11);
				String Country = this.rs.getString(12);
				String HomePhone = this.rs.getString(13);
				String Extension = this.rs.getString(14);
				String Photo = this.rs.getString(15);
				String Notes = this.rs.getString(16);
				int ReportsTo = this.rs.getInt(17);
				
				listaDeEmployees.add(new Employees(EmployeeId, LastName, FirstName, Title, TitleOfCourtesy, BirthDate, HireDate, Address, City, Region, PostalCode, Country, HomePhone, Extension, Photo, Notes, ReportsTo));
				
			}

		} catch (SQLException e) {
			throw new ConexaoException();
		} catch (ClassNotFoundException e) {
			throw new DriverException();
		} finally {
			try {
				if (this.conexao != null) this.conexao.fecharConexao();
				if (this.statement != null) this.statement.close();
			} catch (SQLException e) {
				throw new ConexaoException();
			}
		}
		return listaDeEmployees;
	}

	public Employees selecionarByCod(Integer _customerid) throws ConexaoException, DriverException {
		Employees localizado = null;
		try {
			this.conexao.abrirConexao();
			this.statement = this.conexao.getConexao().prepareStatement(this.SQL_SELECT_BY_CODIGO);
			this.statement.setInt(1, _customerid);
			this.rs = this.statement.executeQuery();

			if (rs.next()) {
				
				int EmployeeId = this.rs.getInt(1);
				String LastName = this.rs.getString(2);
				String FirstName = this.rs.getString(3);
				String Title = this.rs.getString(4);
				String TitleOfCourtesy = this.rs.getString(5);
				Date BirthDate = this.rs.getDate(6);
				Date HireDate = this.rs.getDate(7);
				String Address = this.rs.getString(8);
				String City = this.rs.getString(9);
				String Region = this.rs.getString(10);
				String PostalCode = this.rs.getString(11);
				String Country = this.rs.getString(12);
				String HomePhone = this.rs.getString(13);
				String Extension = this.rs.getString(14);
				String Photo = this.rs.getString(15);
				String Notes = this.rs.getString(16);
				int ReportsTo = this.rs.getInt(17);;
				
				localizado = new Employees(EmployeeId, LastName, FirstName, Title, TitleOfCourtesy, BirthDate, HireDate, Address, City, Region, PostalCode, Country, HomePhone, Extension, Photo, Notes, ReportsTo);
				
			}

		} catch (SQLException e) {
			throw new ConexaoException();
		} catch (ClassNotFoundException e) {
			throw new DriverException();
		} finally {
			try {
				if (this.conexao != null) this.conexao.fecharConexao();
				if (this.statement != null) this.statement.close();
			} catch (SQLException e) {
				throw new ConexaoException();
			}
		}
		return localizado;
	}

}
